package com.hk.excel;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import jxl.*;

import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

/**
 * 用于解析Excel数据
 */
public class ExcelUtil {

    private static final String TSPWaveSpeedTable = "《炮点参数表》";
    private static final String TSPWaveSpeedStartLabel = "检波器";

    private static final String TSPPaoDianArgsTable = "《波速分布参数表》";
    private static final String TSPPaoDianArgsStartLabel = "距离(m)";

    private static final String ConclusionTable = "《预报结论表》";
    private static final String ConclusionStartLabel = "开始里程";


    private static final String ZuanTanZuanKongTable = "《钻孔柱状图数据表》";
    private static final String ZuanTanZuanKongStartLabel = "孔位编号";

    private static final String ZuanTanRecordTable = "《钻探记录表》";
    private static final String ZuanTanRecordStartLabel = "钻探开始时间";

    private static final String ZuanTanDiCengTable = "《地层信息表》";
    private static final String ZuanTanDiCengStartLabel = "地层代号";


    /**
     * 解析超前水平钻探钻孔柱状图
     *
     * @param inputStream
     * @return
     */
    public static JSONArray parseZuanTanZuanKong(InputStream inputStream) {
        JSONArray dataArray = new JSONArray();
        Workbook workbook = openWorkBook(inputStream);
        try {
            Sheet sheet = workbook.getSheet(0);
            int sheetRowNumber = sheet.getRows();
            LabelCell startMileLabel = sheet.findLabelCell(ZuanTanZuanKongStartLabel);
            int rowNumberIndex = startMileLabel.getRow();
            int columnIndex = startMileLabel.getColumn();
            rowNumberIndex++;
            while (rowNumberIndex < sheetRowNumber) {
                Cell kongWeiNumberCell = sheet.getCell(columnIndex, rowNumberIndex);
                if (kongWeiNumberCell.getContents().contains(ZuanTanRecordTable)) {
                    break;
                }
                Cell dis2GongDing = sheet.getCell(columnIndex + 1, rowNumberIndex);
                Cell dis2ZhongXinLineCell = sheet.getCell(columnIndex + 2, rowNumberIndex);
                Cell kaiKongLiJiaoCell = sheet.getCell(columnIndex + 3, rowNumberIndex);

                Cell kaiKongPianJiao = sheet.getCell(columnIndex + 4, rowNumberIndex);
                Cell zuanKongZhiJingCell = sheet.getCell(columnIndex + 5, rowNumberIndex);
                Cell zuanJiModelCell = sheet.getCell(columnIndex + 6, rowNumberIndex);
                Cell startDateTimeCell = sheet.getCell(columnIndex + 7, rowNumberIndex);

                Cell stopDateTimeCell = sheet.getCell(columnIndex + 8, rowNumberIndex);
                Cell markCell = sheet.getCell(columnIndex + 9, rowNumberIndex);


                JSONObject current = new JSONObject();
                if (kongWeiNumberCell.getType() != CellType.EMPTY) {
                    current.put("kwbh", kongWeiNumberCell.getContents());
                }
                if (dis2GongDing.getType() != CellType.EMPTY) {
                    current.put("jgdjl", dis2GongDing.getContents());
                }
                if (dis2ZhongXinLineCell.getType() != CellType.EMPTY) {
                    current.put("jzxxjl", dis2ZhongXinLineCell.getContents());
                }
                if (kaiKongLiJiaoCell.getType() != CellType.EMPTY) {
                    current.put("kwljangle", kaiKongLiJiaoCell.getContents());
                }


                if (kaiKongPianJiao.getType() != CellType.EMPTY) {
                    current.put("kwpjangle", kaiKongPianJiao.getContents());
                }
                if (zuanKongZhiJingCell.getType() != CellType.EMPTY) {
                    current.put("zkzj", zuanKongZhiJingCell.getContents());
                }
                if (zuanJiModelCell.getType() != CellType.EMPTY) {
                    current.put("zjcode", zuanJiModelCell.getContents());
                }
                if (startDateTimeCell.getType() != CellType.EMPTY) {
                    current.put("kssj", startDateTimeCell.getContents());
                }

                if (stopDateTimeCell.getType() != CellType.EMPTY) {
                    current.put("jssj", stopDateTimeCell.getContents());
                }
                if (markCell.getType() != CellType.EMPTY) {
                    current.put("remark", markCell.getContents());
                }
                dataArray.add(current);
                rowNumberIndex++;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            closeWorkBook(workbook);
        }
        return jsonArrayPostProcess(dataArray);
    }


    /**
     * 解析超前水平钻探钻孔柱状图
     *
     * @param inputStream
     * @return
     */
    public static JSONArray parseZuanTanDiCeng(InputStream inputStream) {
        JSONArray dataArray = new JSONArray();
        Workbook workbook = openWorkBook(inputStream);
        try {
            Sheet sheet = workbook.getSheet(0);
            int sheetRowNumber = sheet.getRows();
            LabelCell startLabel = sheet.findLabelCell(ZuanTanDiCengStartLabel);
            int rowNumberIndex = startLabel.getRow();
            int columnIndex = startLabel.getColumn();
            rowNumberIndex++;
            while (rowNumberIndex < sheetRowNumber) {
                Cell diCengCell = sheet.getCell(columnIndex, rowNumberIndex);
                Cell diCengLiChengCell = sheet.getCell(columnIndex + 1, rowNumberIndex);
                Cell ticknessCell = sheet.getCell(columnIndex + 2, rowNumberIndex);
                Cell chuShuiWeiZhiCell = sheet.getCell(columnIndex + 3, rowNumberIndex);

                Cell chuShuiLiangCell = sheet.getCell(columnIndex + 4, rowNumberIndex);
                Cell caiYangWeiZhiCell = sheet.getCell(columnIndex + 5, rowNumberIndex);
                Cell descCell = sheet.getCell(columnIndex + 6, rowNumberIndex);

                JSONObject current = new JSONObject();
                if (diCengCell.getType() != CellType.EMPTY) {
                    current.put("dcdh", diCengCell.getContents());
                }
                if (diCengLiChengCell.getType() != CellType.EMPTY) {
                    current.put("dclcz", diCengLiChengCell.getContents());
                }
                if (ticknessCell.getType() != CellType.EMPTY) {
                    current.put("fchd", ticknessCell.getContents());
                }
                if (chuShuiWeiZhiCell.getType() != CellType.EMPTY) {
                    current.put("cslcz", chuShuiWeiZhiCell.getContents());
                }


                if (chuShuiLiangCell.getType() != CellType.EMPTY) {
                    current.put("csl", chuShuiLiangCell.getContents());
                }
                if (caiYangWeiZhiCell.getType() != CellType.EMPTY) {
                    current.put("cywz", caiYangWeiZhiCell.getContents());
                }
                if (descCell.getType() != CellType.EMPTY) {
                    current.put("gcdzjj", descCell.getContents());
                }
                dataArray.add(current);
                rowNumberIndex++;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            closeWorkBook(workbook);
        }
        return jsonArrayPostProcess(dataArray);
    }


    /**
     * 解析超前水平钻探钻探记录表
     *
     * @param inputStream
     * @return
     */
    public static JSONArray parseZuanTanRecord(InputStream inputStream) {
        JSONArray dataArray = new JSONArray();
        Workbook workbook = openWorkBook(inputStream);
        try {
            Sheet sheet = workbook.getSheet(0);
            int sheetRowNumber = sheet.getRows();
            LabelCell startMileLabel = sheet.findLabelCell(ZuanTanRecordStartLabel);
            int rowNumberIndex = startMileLabel.getRow();
            int columnIndex = startMileLabel.getColumn();
            rowNumberIndex++;
            while (rowNumberIndex < sheetRowNumber) {
                Cell startDateTimeCell = sheet.getCell(columnIndex, rowNumberIndex);
                if (startDateTimeCell.getContents().contains(ZuanTanDiCengTable)) {
                    break;
                }
                Cell stopDateTimeCell = sheet.getCell(columnIndex + 1, rowNumberIndex);
                Cell depthCell = sheet.getCell(columnIndex + 2, rowNumberIndex);
                Cell yaLiCell = sheet.getCell(columnIndex + 3, rowNumberIndex);

                Cell zuanSuCell = sheet.getCell(columnIndex + 4, rowNumberIndex);
                Cell shuiYaCell = sheet.getCell(columnIndex + 5, rowNumberIndex);
                Cell shuiLiangCell = sheet.getCell(columnIndex + 6, rowNumberIndex);
                Cell descCell = sheet.getCell(columnIndex + 7, rowNumberIndex);

                Cell zuoBiaoCell = sheet.getCell(columnIndex + 8, rowNumberIndex);


                JSONObject current = new JSONObject();
                if (startDateTimeCell.getType() != CellType.EMPTY) {
                    current.put("kssj", startDateTimeCell.getContents());
                }
                if (stopDateTimeCell.getType() != CellType.EMPTY) {
                    current.put("jssj", stopDateTimeCell.getContents());
                }
                if (depthCell.getType() != CellType.EMPTY) {
                    current.put("zksd", depthCell.getContents());
                }
                if (yaLiCell.getType() != CellType.EMPTY) {
                    current.put("zkpressure", yaLiCell.getContents());
                }


                if (zuanSuCell.getType() != CellType.EMPTY) {
                    current.put("zkspeed", zuanSuCell.getContents());
                }
                if (shuiYaCell.getType() != CellType.EMPTY) {
                    current.put("kwwaterpre", shuiYaCell.getContents());
                }
                if (shuiLiangCell.getType() != CellType.EMPTY) {
                    current.put("kwwaterspe", shuiLiangCell.getContents());
                }
                if (descCell.getType() != CellType.EMPTY) {
                    current.put("dzms", descCell.getContents());
                }

                if (zuoBiaoCell.getType() != CellType.EMPTY) {
                    current.put("kwzbxl", zuoBiaoCell.getContents());
                }
                dataArray.add(current);
                rowNumberIndex++;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            closeWorkBook(workbook);
        }
        return jsonArrayPostProcess(dataArray);
    }


    /**
     * 解析TSP波速分布参数
     *
     * @param inputStream
     * @return
     */
    public static JSONArray parseTSPWaveSpeed(InputStream inputStream) {
        JSONArray dataArray = new JSONArray();
        Workbook workbook = openWorkBook(inputStream);

        try {
            Sheet sheet = workbook.getSheet(0);
            int sheetRowNumber = sheet.getRows();
            LabelCell startMileLabel = sheet.findLabelCell(TSPWaveSpeedStartLabel);
            int rowNumberIndex = startMileLabel.getRow();
            int columnIndex = startMileLabel.getColumn();
            rowNumberIndex++;
            while (rowNumberIndex < sheetRowNumber) {
                Cell jianBoQiCell = sheet.getCell(columnIndex, rowNumberIndex);
                if (jianBoQiCell.getContents().contains(ConclusionTable)) {
                    break;
                }
                Cell jianBoQiNumberCell = sheet.getCell(columnIndex + 1, rowNumberIndex);
                Cell waveType = sheet.getCell(columnIndex + 2, rowNumberIndex);
                Cell duanMianMile = sheet.getCell(columnIndex + 3, rowNumberIndex);
                Cell speedCell = sheet.getCell(columnIndex + 4, rowNumberIndex);
                Cell VPVSCell = sheet.getCell(columnIndex + 5, rowNumberIndex);
                Cell boSongBiCell = sheet.getCell(columnIndex + 6, rowNumberIndex);
                Cell miDuCell = sheet.getCell(columnIndex + 7, rowNumberIndex);

                JSONObject current = new JSONObject();

                if (jianBoQiCell.getType() != CellType.EMPTY) {
                    current.put("jbq", jianBoQiCell.getContents());
                }
                if (jianBoQiNumberCell.getType() != CellType.EMPTY) {
                    current.put("jbxh", jianBoQiNumberCell.getContents());
                }
                if (waveType.getType() != CellType.EMPTY) {
                    current.put("bx", waveType.getContents());
                }
                if (duanMianMile.getType() != CellType.EMPTY) {
                    current.put("sdlcz", duanMianMile.getContents());
                }


                if (speedCell.getType() != CellType.EMPTY) {
                    current.put("bs", speedCell.getContents());
                }
                if (VPVSCell.getType() != CellType.EMPTY) {
                    current.put("vps", VPVSCell.getContents());
                }
                if (boSongBiCell.getType() != CellType.EMPTY) {
                    current.put("bsb", boSongBiCell.getContents());
                }
                if (miDuCell.getType() != CellType.EMPTY) {
                    current.put("md", miDuCell.getContents());
                }
                dataArray.add(current);
                rowNumberIndex++;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            closeWorkBook(workbook);
        }
        return jsonArrayPostProcess(dataArray);
    }

    /**
     * 解析TSP炮点参数
     *
     * @param inputStream
     * @return
     */
    public static JSONArray parseTSPPaoDianArgs(InputStream inputStream) {
        JSONArray dataArray = new JSONArray();
        Workbook workbook = openWorkBook(inputStream);

        try {
            Sheet sheet = workbook.getSheet(0);
            int sheetRowNumber = sheet.getRows();
            LabelCell startLabel = sheet.findLabelCell(TSPPaoDianArgsStartLabel);
            int rowNumberIndex = startLabel.getRow();
            int columnIndex = startLabel.getColumn();
            rowNumberIndex++;
            while (rowNumberIndex < sheetRowNumber) {
                Cell distanceCell = sheet.getCell(columnIndex, rowNumberIndex);
                if (distanceCell.getContents().contains(TSPPaoDianArgsTable)) {
                    break;
                }
                Cell deepCell = sheet.getCell(columnIndex + 1, rowNumberIndex);
                Cell heightCell = sheet.getCell(columnIndex + 2, rowNumberIndex);
                Cell qingJiaoCell = sheet.getCell(columnIndex + 3, rowNumberIndex);
                Cell fangWeiJiaoCell = sheet.getCell(columnIndex + 4, rowNumberIndex);
                Cell yaoLiangCell = sheet.getCell(columnIndex + 5, rowNumberIndex);

                JSONObject current = new JSONObject();
                if (distanceCell.getType() != CellType.EMPTY) {
                    current.put("pdjl", distanceCell.getContents());
                }

                if (deepCell.getType() != CellType.EMPTY) {
                    current.put("pdsd", deepCell.getContents());
                }
                if (heightCell.getType() != CellType.EMPTY) {
                    current.put("height", heightCell.getContents());
                }
                if (qingJiaoCell.getType() != CellType.EMPTY) {
                    current.put("qj", qingJiaoCell.getContents());
                }
                if (fangWeiJiaoCell.getType() != CellType.EMPTY) {
                    current.put("fwj", fangWeiJiaoCell.getContents());
                }
                if (yaoLiangCell.getType() != CellType.EMPTY) {
                    current.put("yl", yaoLiangCell.getContents());
                }
                dataArray.add(current);
                rowNumberIndex++;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            closeWorkBook(workbook);
        }
        return jsonArrayPostProcess(dataArray);
    }


    /**
     * 解析结论数据
     *
     * @param inputStream
     * @return
     */
    public static JSONArray parseConclusion(InputStream inputStream) {
        JSONArray dataArray = new JSONArray();
        Workbook workbook = openWorkBook(inputStream);
        try {
            Sheet sheet = workbook.getSheet(0);
            int sheetRowNumber = sheet.getRows();
            LabelCell startMileLabel = sheet.findLabelCell(ConclusionStartLabel);
            int rowNumberIndex = startMileLabel.getRow();
            int columnIndex = startMileLabel.getColumn();
            rowNumberIndex++;
            while (rowNumberIndex < sheetRowNumber) {
                Cell startMileCell = sheet.getCell(columnIndex, rowNumberIndex);
                Cell stopMileCell = sheet.getCell(columnIndex + 1, rowNumberIndex);
                Cell conclusionCell = sheet.getCell(columnIndex + 2, rowNumberIndex);
                JSONObject current = new JSONObject();
                if (startMileCell.getType() != CellType.EMPTY) {
                    current.put("startMile", ((LabelCell) startMileCell).getString());
                }
                if (stopMileCell.getType() != CellType.EMPTY) {
                    current.put("stopMile", ((LabelCell) stopMileCell).getString());
                }
                if (conclusionCell.getType() != CellType.EMPTY) {
                    current.put("conclusion", ((LabelCell) conclusionCell).getString());
                }
                dataArray.add(current);

                rowNumberIndex++;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            closeWorkBook(workbook);
        }
        return jsonArrayPostProcess(dataArray);
    }


    /**
     * 打开Excel表
     *
     * @param inputStream
     * @return
     */
    private static Workbook openWorkBook(InputStream inputStream) {
        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(inputStream);
        } catch (Exception e) {
            e.printStackTrace();
            if (workbook != null) {
                workbook.close();
            }
        }

        //Close input stream
        if (inputStream != null) {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return workbook;
    }


    /**
     * 关闭Excel表
     *
     * @param workbook
     */
    private static void closeWorkBook(Workbook workbook) {
        if (workbook != null) {
            workbook.close();
        }
    }


    /**
     * JSONArray 后处理器，如果此JSONObject只含有dataNumber字段则删除该JSONObject
     *
     * @return
     */
    private static JSONArray jsonArrayPostProcess(JSONArray jsonArray) {
        Iterator<Object> iterator = jsonArray.iterator();
        while (iterator.hasNext()) {
            JSONObject current = (JSONObject) iterator.next();
            if (current.isEmpty()) {
                iterator.remove();
            }
        }
        return jsonArray;
    }

}
